﻿namespace Sofire.Data
{
    using System;
    using System.Collections.Generic;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Data;

    /// <summary>
    /// 基于 Miscsoft SQL Server 查询与交互的操作引擎。
    /// </summary>
    public class MsSqlQuery : QueryEngineBase
    {
        #region Fields

        const string IntegratedSecurityFormat = "Data Source={0};Initial Catalog={1};Integrated Security=True;Connect Timeout={2};";
        const string UserPasswordFormat = "Data Source={0};Initial Catalog={1};User ID={2};Password={3};Connect Timeout={4};";

        #endregion Fields

        #region Constructors

        /// <summary>
        /// 初始化 <see cref="Sofire.Data.MsSqlQuery"/> 的新实例。
        /// </summary>
        /// <param name="connectionString">连接字符串。</param>
        public MsSqlQuery(string connectionString)
            : base(QueryEngineProvider.Microsoft_SQL_Server, connectionString)
        {
        }

        /// <summary>
        /// 初始化 <see cref="Sofire.Data.MsSqlQuery"/> 的新实例。
        /// </summary>
        /// <param name="dataSource">数据源。</param>
        /// <param name="initialCatalog">数据库。</param>
        public MsSqlQuery(string dataSource, string initialCatalog)
            : this(dataSource, initialCatalog, 15)
        {
        }

        /// <summary>
        /// 初始化 <see cref="Sofire.Data.MsSqlQuery"/> 的新实例。
        /// </summary>
        /// <param name="dataSource">数据源。</param>
        /// <param name="initialCatalog">数据库。</param>
        /// <param name="connectTimeout">指示连接超时时限。</param>
        public MsSqlQuery(string dataSource, string initialCatalog, int connectTimeout)
            : this(string.Format(IntegratedSecurityFormat, dataSource, initialCatalog, connectTimeout))
        {
        }

        /// <summary>
        /// 初始化 <see cref="Sofire.Data.MsSqlQuery"/> 的新实例。
        /// </summary>
        /// <param name="dataSource">数据源。</param>
        /// <param name="initialCatalog">数据库。</param>
        /// <param name="userID">登录账户。</param>
        /// <param name="passwrod">登录密码。</param>
        public MsSqlQuery(string dataSource, string initialCatalog, string userID, string passwrod)
            : this(dataSource, initialCatalog, userID, passwrod, 15)
        {
        }

        /// <summary>
        /// 初始化 <see cref="Sofire.Data.MsSqlQuery"/> 的新实例。
        /// </summary>
        /// <param name="dataSource">数据源。</param>
        /// <param name="initialCatalog">数据库。</param>
        /// <param name="userID">登录账户。</param>
        /// <param name="passwrod">登录密码。</param>
        /// <param name="connectTimeout">指示连接超时时限。</param>
        public MsSqlQuery(string dataSource, string initialCatalog, string userID, string passwrod, int connectTimeout)
            : this(string.Format(UserPasswordFormat, dataSource, initialCatalog, userID, passwrod, connectTimeout))
        {
        }

        #endregion Constructors

        #region Methods

        /// <summary>
        /// 将当前的数据库备份到指定路径。
        /// <para>说明：该备份模式为 完整备份。</para>
        /// </summary>
        /// <param name="filename">备份文件路径。</param>
        public NonQueryResult BackupFile(string filename)
        {
            return this.BackupFile(this.CreateConnection().Database, filename);
        }

        /// <summary>
        /// 将给定的数据库备份到指定路径。
        /// <para>说明：该备份模式为 完整备份。</para>
        /// </summary>
        /// <param name="dbname">需要备份的数据库。</param>
        /// <param name="filename">备份文件路径。</param>
        public NonQueryResult BackupFile(string dbname, string filename)
        {
            return this.ExecuteNonQuery("BACKUP DATABASE @database TO DISK = @filename", "@database", dbname, "@filename", filename);
        }

        /// <summary>
        /// 创建并返回一个 <see cref="System.Data.SqlClient.SqlCommandBuilder"/> 查询命令生成器。
        /// </summary>
        /// <returns>返回一个查询命令生成器。</returns>
        public override DbCommandBuilder CreateCommandBuilder()
        {
            return new SqlCommandBuilder();
        }

        /// <summary>
        /// 创建并返回一个 <see cref="System.Data.SqlClient.SqlConnection"/> 数据源。
        /// </summary>
        /// <returns>返回一个数据库的连接。</returns>
        public override DbConnection CreateConnection()
        {
            return new SqlConnection(this.ConnectionString);
        }

        /// <summary>
        /// 创建并返回一个 <see cref="System.Data.SqlClient.SqlDataAdapter"/> 数据适配器。
        /// </summary>
        /// <param name="command">命令对象。</param>
        /// <returns>返回一个数据适配器。</returns>
        public override DbDataAdapter CreateDataAdapter(DbCommand command)
        {
            return new SqlDataAdapter(command as SqlCommand);
        }

        /// <summary>
        /// 指定存储过程的名称，获取存储过程的参数名集合。
        /// </summary>
        /// <param name="procedureName">存储过程的名称。</param>
        /// <returns>返回一组表示存储过程参数名集合的数组。</returns>
        public override Result<string[]> GetProcedureParameters(string procedureName)
        {
            List<string> list = new List<string>();
            var r = this.ExecuteReader((reader) =>
                   {
                       while(reader.Read())
                       {
                           list.Add(reader.GetString(0));
                       }
                   }, "SELECT a.Name FROM syscolumns a JOIN systypes b ON a.xtype=b.xusertype WHERE a.id=object_id(@procedureName)", "@procedureName", procedureName);
            if(!r.IsSucceed) return r.Exception;
            return list.ToArray();
        }

        /// <summary>
        /// 立即将给定的数据库切换至脱机模式。
        /// <para>说明：脱机后该数据库将无法访问。</para>
        /// </summary>
        /// <param name="dbname">将要切换至脱机模式的数据库。</param>
        public NonQueryResult OffineMode(string dbname)
        {
            // 也可以使用 sp_dboption 存储过程
            return this.ExecuteNonQuery("ALTER DATABASE " + dbname + " SET OFFLINE WITH ROLLBACK IMMEDIATE;");
        }

        /// <summary>
        /// 将给定的备份文件替换到当前数据库。
        /// <para>说明：执行此方法前，必须将当前连接字符串的默认为 master 数据库，并且至少执行 <see cref="Sofire.Data.MsSqlQuery.OffineMode"/> 方法或 <see cref="Sofire.Data.MsSqlQuery.SingeUserMode"/> 方法。</para>
        /// </summary>
        /// <param name="filename">备份文件路径。</param>
        public NonQueryResult RestoreReplaceFile(string filename)
        {
            return this.RestoreReplaceFile(this.CreateConnection().Database, filename);
        }

        /// <summary>
        /// 将给定的备份文件替换到给定的数据库。
        /// <para>说明：执行此方法前，必须将当前连接字符串的默认为 master 数据库。并执行 <see cref="Sofire.Data.MsSqlQuery.OffineMode"/> 方法将数据库脱机。</para>
        /// </summary>
        /// <param name="dbname">需要恢复的数据库。</param>
        /// <param name="filename">备份文件路径。</param>
        public NonQueryResult RestoreReplaceFile(string dbname, string filename)
        {
            //RESTORE DATABASE 后面无法跟参数。
            return this.ExecuteNonQuery("RESTORE DATABASE " + dbname + " FROM DISK=@filename WITH REPLACE;", "@filename", filename);
        }

        /// <summary>
        /// 立即将给定的数据库切换至单用户模式。
        /// <para>说明：单用户模式后，数据库将拒绝其他任何用户访问数据库。</para>
        /// </summary>
        /// <param name="dbname">将要切换至单用户模式的数据库。</param>
        public NonQueryResult SingeUserMode(string dbname)
        {
            return this.ExecuteNonQuery("ALTER DATABASE " + dbname + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE;");
        }

        #endregion Methods

        /// <summary>
        /// 描述指定的 <see cref="System.Data.Common.DbParameter"/>。
        /// </summary>
        /// <param name="parameter">一个 <see cref="System.Data.Common.DbParameter"/> 的实例。</param>
        /// <returns>返回参数描述的 <see cref="System.String"/> 值。</returns>
        public override string DescribeParameter(DbParameter parameter)
        {
            var p = parameter as SqlParameter;
            string desc = p.ParameterName;

            switch(p.Direction)
            {
                case ParameterDirection.InputOutput:
                    desc += " IN OUT";
                    break;
                case ParameterDirection.Output:
                    desc += " OUT";
                    break;
            }
            desc += " " + p.SqlDbType;
            switch(p.SqlDbType)
            {
                case SqlDbType.Char:
                case SqlDbType.NChar:
                case SqlDbType.NVarChar:
                case SqlDbType.Decimal:
                case SqlDbType.Money:
                case SqlDbType.Float:
                case SqlDbType.VarChar:
                case SqlDbType.Binary:
                case SqlDbType.VarBinary:
                    desc += "(" + p.Size + ")";
                    break;
            }
            return desc;
        }
    }
}